// ==========================================
// Excel批量导入资产 API
// ==========================================

// 需要先安装依赖: npm install xlsx

const XLSX = require('xlsx');

// 批量导入资产（管理员）
app.post('/api/admin/asset/import', upload.single('file'), async (req, res) => {
    const connection = await pool.getConnection();

    try {
        const { adminKey } = req.body;

        const ADMIN_SECRET_KEY = 'repair2024admin';
        if (adminKey !== ADMIN_SECRET_KEY) {
            return res.json({ code: 1003, success: false, message: '密钥错误' });
        }

        if (!req.file) {
            return res.json({ code: 1001, success: false, message: '请上传Excel文件' });
        }

        // 读取Excel文件
        const workbook = XLSX.read(req.file.buffer, { type: 'buffer' });
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];

        // 转换为JSON
        const data = XLSX.utils.sheet_to_json(worksheet);

        if (!data || data.length === 0) {
            return res.json({ code: 2001, success: false, message: 'Excel文件为空' });
        }

        await connection.beginTransaction();

        let successCount = 0;
        let failCount = 0;
        const errors = [];

        for (let i = 0; i < data.length; i++) {
            const row = data[i];

            try {
                // 验证必填字段
                if (!row['资产编号'] || !row['资产名称'] || !row['分类ID']) {
                    errors.push(`第${i + 2}行：缺少必填字段`);
                    failCount++;
                    continue;
                }

                // 插入资产
                await connection.execute(
                    `INSERT INTO assets (
                        asset_code, name, category_id, lab_id, brand, model, 
                        purchase_price, purchase_date, status, remark
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                    [
                        row['资产编号'],
                        row['资产名称'],
                        row['分类ID'],
                        row['机房ID'] || null,
                        row['品牌'] || null,
                        row['型号'] || null,
                        row['采购价格'] || null,
                        row['采购日期'] || null,
                        row['状态'] || 'in_use',
                        row['备注'] || null
                    ]
                );

                successCount++;
            } catch (error) {
                // 可能是重复的资产编号
                if (error.code === 'ER_DUP_ENTRY') {
                    errors.push(`第${i + 2}行：资产编号已存在`);
                } else {
                    errors.push(`第${i + 2}行：${error.message}`);
                }
                failCount++;
            }
        }

        await connection.commit();

        res.json({
            code: 0,
            success: true,
            message: '导入完成',
            data: {
                total: data.length,
                successCount,
                failCount,
                errors: errors.slice(0, 10) // 只返回前10个错误
            }
        });

    } catch (error) {
        await connection.rollback();
        console.error('批量导入失败:', error);
        res.json({ code: 9999, success: false, message: '导入失败: ' + error.message });
    } finally {
        connection.release();
    }
});

// 下载Excel模板
app.get('/api/admin/asset/template', (req, res) => {
    const { adminKey } = req.query;

    const ADMIN_SECRET_KEY = 'repair2024admin';
    if (adminKey !== ADMIN_SECRET_KEY) {
        return res.json({ code: 1003, success: false, message: '密钥错误' });
    }

    // 创建示例数据
    const template = [
        {
            '资产编号': 'PC-A101-001',
            '资产名称': '戴尔台式电脑',
            '分类ID': 6,
            '机房ID': 'A101',
            '品牌': 'Dell',
            '型号': 'OptiPlex 7090',
            '采购价格': 4500.00,
            '采购日期': '2024-01-15',
            '状态': 'in_use',
            '备注': '示例数据，请删除此行'
        }
    ];

    // 创建工作簿
    const wb = XLSX.utils.book_new();
    const ws = XLSX.utils.json_to_sheet(template);

    // 设置列宽
    ws['!cols'] = [
        { wch: 15 }, // 资产编号
        { wch: 20 }, // 资产名称
        { wch: 10 }, // 分类ID
        { wch: 12 }, // 机房ID
        { wch: 12 }, // 品牌
        { wch: 15 }, // 型号
        { wch: 12 }, // 采购价格
        { wch: 12 }, // 采购日期
        { wch: 10 }, // 状态
        { wch: 20 }  // 备注
    ];

    XLSX.utils.book_append_sheet(wb, ws, '资产导入模板');

    // 生成buffer
    const buffer = XLSX.write(wb, { type: 'buffer', bookType: 'xlsx' });

    res.setHeader('Content-Disposition', 'attachment; filename=asset_import_template.xlsx');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.send(buffer);
});
